# _*_ coding:utf-8 _*_
# @File  : exchange_strategy.py
# @Time  : 2021-10-21  14:50
# @Author: zizle


# 交易策略接口
import datetime

import pandas as pd
from fastapi import APIRouter, Body, Depends, Path, Query
from pydantic import BaseModel
from fastapi.encoders import jsonable_encoder
from db import FAConnection
from interfaces.depends import logged_require
from hutool.tools import datetime_handler
from status import r_status
from category import CFFEX_VARIETY, SPECIAL_VARIETY

strategy_api = APIRouter()


class StrategyItem(BaseModel):
    type: int
    ykb: float
    variety_en: str
    description: str
    fund: float
    strategy: str
    strategy_price: float
    target_price: float
    cutloss_price: float
    is_open: int = 1


@strategy_api.post('/strategy/', summary='提交一条交易策略')
async def create_strategy(person: dict = Depends(logged_require), strategy_item: StrategyItem = Body(...)):
    if strategy_item.type in [1, 2]:
        # 计算盈亏比
        yl = strategy_item.target_price - strategy_item.strategy_price
        ks = strategy_item.cutloss_price - strategy_item.strategy_price
        if abs(yl/ks) < 2:
            return {'code': r_status.VALIDATE_ERROR, 'message': '策略盈亏比需大于2:1。'}
        if strategy_item.variety_en not in CFFEX_VARIETY + list(SPECIAL_VARIETY.keys()):
            if strategy_item.target_price != 0:
                # 检测价格区间
                if abs(round(strategy_item.strategy_price / strategy_item.target_price - 1, 2)) < 0.02:
                    return {'code': r_status.VALIDATE_ERROR, 'message': '价格区间需为2%以上。'}
    else:
        if strategy_item.ykb < 2:
            return {'code': r_status.VALIDATE_ERROR, 'message': '策略盈亏比需大于2:1。'}
    is_open = 1 if strategy_item.is_open else 0
    json_data = jsonable_encoder(strategy_item)
    # 处理数据保存入库
    json_data['creator'] = person['uid']
    json_data['is_open'] = is_open
    # 存到数据库
    save_sql = 'INSERT INTO info_strategy (creator,type,variety_en,description,fund,strategy_price,target_price,' \
               'cutloss_price,strategy,is_open) ' \
               'VALUES (%(creator)s,%(type)s,%(variety_en)s,%(description)s,%(fund)s,%(strategy_price)s,' \
               '%(target_price)s,%(cutloss_price)s,%(strategy)s,%(is_open)s);'
    db = FAConnection(conn_name='发布策略')
    _, success = db.insert(save_sql, param=json_data)
    if success:
        return {'code': r_status.CREATED_SUCCESS, 'message': '发布策略成功!'}
    else:
        return {'code': r_status.UNKNOWN_ERROR, 'message': '发布策略失败!服务器错误。'}


def format_strategy_response(data_list):  # 格式化策略数据
    response_data = []
    for item in data_list:
        detail = eval(item['strategy'])

        # 转换数据类型
        p_num = 4 if item['variety_en'] in SPECIAL_VARIETY.keys() else 2
        sp, tp, cp = item['strategy_price'], item['target_price'], item['cutloss_price']
        sp = int(sp) if int(sp) == round(sp, p_num) else round(sp, p_num)
        tp = int(tp) if int(tp) == round(tp, p_num) else round(tp, p_num)
        cp = int(cp) if int(cp) == round(cp, p_num) else round(cp, p_num)

        # 根据type显示策略文字内容
        if item['type'] == 1:  # 单边策略
            detail = detail[0]
            v_name = ''
            if item['variety_en'] in SPECIAL_VARIETY.keys():
                v_name = SPECIAL_VARIETY.get(item['variety_en'])
            direction = '做多' if detail['direction'] == 'B' else '做空'
            strategy = f'【交易策略】建议于{sp}{detail["price_unit"]}附近{direction}{v_name}{detail["contract"]}，' \
                       f'目标{tp}{detail["price_unit"]}，止损{cp}{detail["price_unit"]}。' \
                       f'{item["description"]}'
            hands_text = detail['hands']
        elif item['type'] == 2:  # 双品种套利策略
            v1_data, v2_data = detail
            d1 = '做多' if v1_data["direction"] == 'B' else '做空'
            d2 = '做多' if v2_data['direction'] == 'B' else '做空'
            v1_name = v2_name = ''
            if v1_data['variety_en'] in SPECIAL_VARIETY.keys():
                v1_name = SPECIAL_VARIETY.get(v1_data['variety_en'])
                v2_name = SPECIAL_VARIETY.get(v2_data['variety_en'])

            # 组织数据
            strategy = f'【套利策略】{v1_name}{v1_data["contract"]}与{v2_name}{v2_data["contract"]}合约价差为{sp}时，' \
                       f'建议{d1}{v1_name}{v1_data["contract"]},{d2}{v2_name}{v2_data["contract"]}，' \
                       f'价差为{tp}附近止盈，止损价差为{cp}附近。{item["description"]}'

            hands_text = v1_data['hands']
        elif item['type'] == 3:  # 多品种套利策略
            strategy = '【套利策略】建议同时'
            for stv_item in detail:  # stv= strategy_variety
                d = '做多' if stv_item['direction'] == 'B' else '做空'
                if stv_item['variety_en'] in SPECIAL_VARIETY.keys():
                    v_name = SPECIAL_VARIETY.get(stv_item['variety_en'], '')
                strategy += f'{d}{v_name}{stv_item["contract"]},数量:{stv_item["hands"]}手;'
            strategy = strategy[:-1] + '。'
            strategy += item['description']
            hands_text = ''
        else:
            continue
        can_delete = True if (datetime.datetime.now() - item['create_time']).seconds <= 1800 else False
        open_time = item['open_time'].strftime('%Y-%m-%d %H:%M:%S') if item['open_time'] else ''
        close_time = item['close_time'].strftime('%Y-%m-%d %H:%M:%S') if item['close_time'] else ''

        profit = item['profit']
        profit = int(profit) if int(profit) == round(profit, 2) else round(profit, 2)
        response_data.append({
            'id': item['id'], 'type': item['type'], 'create_time': item['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'open_time': open_time, 'close_time': close_time, 'profit': profit,'close_way': item['close_way'],
            'variety_name': item['variety_name'], 'can_delete': can_delete, 'content': strategy, 'hands': hands_text
        })
    return response_data


@strategy_api.get('/strategy/', summary='客户端查询最新交易策略')
async def client_get_strategy(page: int = Query(1, ge=1), page_size: int = Query(8, ge=1), v: str = Query(None)):
    return {'code': r_status.SUCCESS, 'message': '获取策略数据成功!', 'data': [],
            'page': page, 'page_size': page_size, 'total_count': 0, 'total_page': 1}
    start, offset = (page - 1) * page_size, page_size
    db = FAConnection(conn_name='客户端查询策略')
    if v:
        query_sql = 'SELECT SQL_CALC_FOUND_ROWS a.id,a.create_time,a.type,a.variety_en,b.variety_name,' \
                    'a.description,a.open_time,a.close_time,a.close_way,a.profit,a.fund,a.strategy_price,' \
                    'a.target_price,a.cutloss_price,a.strategy ' \
                    'FROM info_strategy AS a INNER JOIN sys_variety AS b ON a.variety_en=b.variety_en ' \
                    'WHERE a.is_delete=0 AND a.variety_en=%s AND a.is_open=1 ' \
                    'ORDER BY a.create_time DESC LIMIT %s,%s;'
        params = [v, start, offset]
    else:
        query_sql = 'SELECT SQL_CALC_FOUND_ROWS a.id,a.create_time,a.type,a.variety_en,b.variety_name,' \
                    'a.description,a.open_time,a.close_time,a.close_way,a.profit,a.fund,a.strategy_price,' \
                    'a.target_price,a.cutloss_price,a.strategy ' \
                    'FROM info_strategy AS a INNER JOIN sys_variety AS b ON a.variety_en=b.variety_en ' \
                    'WHERE a.is_delete=0 AND a.is_open=1 ' \
                    'ORDER BY a.create_time DESC LIMIT %s,%s;'
        params = [start, offset]

    records = db.query(query_sql, param=params, keep_conn=True)

    # 查询页码信息
    # 查询总条数
    total_obj = db.query('SELECT FOUND_ROWS() AS total;')[0]
    total_count = total_obj['total'] if total_obj else 1  # 当前数量
    total_page = (total_count + page_size - 1) // page_size

    response_data = format_strategy_response(records)
    # return {'code': r_status.SUCCESS, 'message': '获取策略数据成功!', 'data': response_data,
    #         'page': page, 'page_size': page_size, 'total_count': total_count, 'total_page': total_page}



@strategy_api.get('/strategy/my/', summary='查询个人交易策略')
async def get_strategy(person: dict = Depends(logged_require),
                       page: int = Query(1, ge=1), page_size: int = Query(8, ge=1)):
    start, offset = (page - 1) * page_size, page_size

    query_sql = 'SELECT SQL_CALC_FOUND_ROWS a.id,a.create_time,a.type,a.variety_en,b.variety_name,' \
                'a.description,a.open_time,a.close_time,a.close_way,a.profit,a.fund,a.strategy_price,a.target_price,' \
                'a.cutloss_price,a.strategy ' \
                'FROM info_strategy AS a INNER JOIN sys_variety AS b ON a.variety_en=b.variety_en ' \
                'WHERE a.is_delete=0 AND a.creator=%s ' \
                'ORDER BY a.create_time DESC LIMIT %s,%s;'

    db = FAConnection(conn_name='查询个人交易策略')
    records = db.query(query_sql, param=[person['uid'], start, offset], keep_conn=True)

    total_obj = db.query('SELECT FOUND_ROWS() AS total;', fetchone=True)[0]
    total_count = total_obj['total'] if total_obj else 1  # 当前数量
    total_page = (total_count + page_size - 1) // page_size

    response_data = format_strategy_response(records)
    return {'code': r_status.SUCCESS, 'message': '查询交易策略成功!', 'data': response_data,
            'page': page, 'page_size': page_size, 'total_page': total_page}


# 用户查询近期的策略数量
@strategy_api.get('/strategy/recentlyCount/', summary='查询近期策略数量')
async def recently_count(person: dict = Depends(logged_require)):
    # current = datetime.datetime.strptime('2021-12-26', '%Y-%m-%d')
    current = datetime.datetime.today()
    # 本年
    year_start = f'{current.year}-01-01'
    year_end = f'{current.year}-12-31'
    # 本月
    month_start, month_end = datetime_handler.get_current_month_start_end(current.year, current.month)
    month_start = month_start.strftime('%Y-%m-%d')
    month_end = month_end.strftime('%Y-%m-%d')
    # 本周
    week = current.weekday()
    week_start = (current + datetime.timedelta(days=-week)).strftime('%Y-%m-%d')
    wee_end = (current + datetime.timedelta(days=6-week)).strftime('%Y-%m-%d')
    # 查询本年数据，进行统计
    query_sql = 'SELECT id,create_time ' \
                'FROM info_strategy WHERE is_delete=0 ' \
                'AND creator=%s AND DATE_FORMAT(create_time,"%%Y-%%m-%%d")>=%s;'
    db_conn = FAConnection()
    df = pd.DataFrame(db_conn.query(query_sql, param=[person['uid'], year_start]))
    if df.empty:
        return {'code': r_status.SUCCESS, 'message': '成功!', 'data': {'year': '-', 'month': '-', 'week': '-'}}
    df['my_date'] = df['create_time'].apply(lambda x: x.strftime('%Y-%m-%d'))
    year_count = df[(df['my_date'] >= year_start) & (df['my_date'] <= year_end)].shape[0]  # 本年计数
    month_count = df[(df['my_date'] >= month_start) & (df['my_date'] <= month_end)].shape[0]  # 本月计数
    week_count = df[(df['my_date'] >= week_start) & (df['my_date'] <= wee_end)].shape[0]  # 本周计数
    year_count = str(year_count) if year_count else '-'
    month_count = str(month_count) if month_count else '-'
    week_count = str(week_count) if week_count else '-'
    return {'code': r_status.SUCCESS, 'message': '查询策略计数成功!',
            'data': {'year': year_count, 'month': month_count, 'week': week_count}}


# 用户查询近x成功率和收益率
@strategy_api.get('/strategy/recentlyRate/', summary='查询今日成功和收益率')
async def recently_rate(person: dict = Depends(logged_require), count: int = Query(20, ge=1)):
    query_sql = 'SELECT id,create_time,close_time,profit,fund ' \
                'FROM info_strategy ' \
                'WHERE close_time IS NOT NULL AND is_delete=0 AND creator=%s ' \
                'ORDER BY close_time DESC LIMIT %s;'
    db_conn = FAConnection()
    df = pd.DataFrame(db_conn.query(query_sql, param=[person['uid'], count]))
    if df.empty:
        return {'code': r_status.SUCCESS, 'message': '没有记录',
                'data': {'success_rate': '-', 'profit_rate': '-'}}
    # 计算成功率和收益率
    success_rate = df[df['profit'] > 0].shape[0] / df.shape[0]
    profit_rate = df['profit'].sum() / df['fund'].sum()
    success_rate = f'{round(success_rate * 100, 2)}%'
    profit_rate = f'{round(profit_rate * 100, 2)}%'
    return {'code': r_status.SUCCESS, 'message': '查询策略比率成功！',
            'data': {'count': df.shape[0], 'success_rate': success_rate, 'profit_rate': profit_rate}}


# 个人策略统计
@strategy_api.get('/strategy/my/tj/', summary='个人策略统计')
async def get_strategy_tj(person: dict = Depends(logged_require)):
    person_id = person['uid']
    sql = 'SELECT id,create_time,profit,fund,open_time,close_time ' \
          'FROM info_strategy ' \
          'WHERE creator=%s AND is_delete=0;'
    db = FAConnection(conn_name='查询统计策略')
    records = db.query(sql, param=[person_id])
    record_df = pd.DataFrame(records)
    # 当策略为空时
    if record_df.empty:
        return {'code': r_status.SUCCESS, 'message': '还没有发布任何策略!', 'data': {}}
    # 筛选出未建仓和运行中的数量
    # open_time与close_time都是空的 = 未建仓
    # open_time不为空，close_time为空 = 运行中
    no_build_df = record_df[pd.isna(record_df['open_time']) & pd.isna(record_df['close_time'])]  # 未建仓
    running_df = record_df[~pd.isna(record_df['open_time']) & pd.isna(record_df['close_time'])]  # 运行中
    df = record_df[~pd.isna(record_df['close_time'])].copy()
    nb_count = no_build_df.shape[0]
    running_count = running_df.shape[0]

    current_datetime = datetime.datetime.today()
    cy, cm = current_datetime.year, current_datetime.month
    df['month'] = df['create_time'].apply(lambda x: x.month)
    df['year'] = df['create_time'].apply(lambda x: x.year)
    # 统计的指标有：
    # 数量： 本月数量、本年数量、总数量
    cm_df = df[df['month'] == cm]
    cy_df = df[df['year'] == cy]
    cm_count, cy_count, total_count = cm_df.shape[0], cy_df.shape[0], df.shape[0]
    # 月数据
    if cm_count == 0:
        cm_success = 0
        cm_success_rate = '-'
        cm_fund = 0
        cm_profit = 0
        cm_profit_rate = '-'
    else:
        cm_success = cm_df[cm_df['profit'] > 0].shape[0]
        cm_success_rate = cm_success / cm_count
        cm_fund = cm_df['fund'].sum()
        cm_profit = cm_df['profit'].sum()
        cm_profit_rate = cm_profit / cm_fund
    # 年数据
    if cy_count == 0:
        cy_success = 0
        cy_success_rate = '-'
        cy_fund = 0
        cy_profit = 0
        cy_profit_rate = '-'
    else:
        cy_success = cy_df[cy_df['profit'] > 0].shape[0]
        cy_success_rate = cy_success / cy_count
        cy_fund = cy_df['fund'].sum()
        cy_profit = cy_df['profit'].sum()
        cy_profit_rate = cy_profit / cy_fund
    # 总数据
    if total_count == 0:
        t_success = 0
        t_success_rate = '-'
        t_fund = 0
        t_profit = 0
        t_profit_rate = '-'
    else:
        t_success = df[df['profit'] > 0].shape[0]
        t_success_rate = t_success / total_count
        t_fund = df['fund'].sum()
        t_profit = df['profit'].sum()
        t_profit_rate = t_profit / t_fund

    rate_dict = {
        'cm_success_rate': cm_success_rate, 'cy_success_rate': cy_success_rate, 't_success_rate': t_success_rate,
        'cm_profit_rate': cm_profit_rate, 'cy_profit_rate': cy_profit_rate,  't_profit_rate': t_profit_rate
    }
    for k, v in rate_dict.items():
        if v == '-':
            continue
        if v == int(v):
            rate_dict[k] = str(int(v))
        else:
            rate_dict[k] = str(round(v * 100, 2)) + '%'

    count_dict = {
        'nb_count': nb_count, 'running_count': running_count,
        'cm_count': cm_count, 'cy_count': cy_count, 't_count': total_count,
        'cm_success': cm_success, 'cy_success': cy_success,  't_success': t_success,
        'cm_fund': cm_fund, 'cy_fund': cy_fund, 't_fund': t_fund,
        'cm_profit': cm_profit, 'cy_profit': cy_profit, 't_profit': t_profit
    }

    for k, v in count_dict.items():
        if v == '-':
            continue
        if v == int(v):
            count_dict[k] = str(int(v))
        else:
            count_dict[k] = str(round(v, 2))

    response_data = {**rate_dict, **count_dict}

    return {'code': r_status.SUCCESS, 'message': '统计策略成功!', 'data': response_data}


@strategy_api.delete('/strategy/{strategy_id}/', summary='删除一个交易策略')
async def delete_strategy(person: dict = Depends(logged_require), strategy_id: int = Path(..., ge=1)):
    # 删除指定ID的策略,如果是创建2天或已成功执行完的策略，将不支持删除
    db = FAConnection(conn_name='查询策略')
    sql = 'SELECT id,create_time,creator FROM info_strategy WHERE id=%s AND creator=%s;'
    record_items = db.query(sql, param=[strategy_id, person['uid']], fetchone=True)
    record_obj = record_items[0]
    if not record_obj:
        return {'code': r_status.NOT_CONTENT, 'message': '删除的数据不存在!'}
    if (datetime.datetime.now() - record_obj['create_time']).seconds >= 1800:
        return {'code': r_status.FORBIDDEN,  'message': '该策略已经发布超过30分钟，暂不支持删除!'}
    # 逻辑删除策略
    db = FAConnection(conn_name='删除策略')
    _, s = db.execute(sql='UPDATE info_strategy SET is_delete=1 WHERE id=%s AND creator=%s;', param=[strategy_id, person['uid']])
    if s:
        return {'code': r_status.SUCCESS,  'message': '删除数据成功!'}
    return {'code': r_status.SERVER_ERROR, 'message': '删除数据失败，服务器错误!'}


@strategy_api.put('/strategy/{strategy_id}/build/', summary='策略开仓')
async def open_strategy(person: dict = Depends(logged_require), strategy_id: int = Path(..., ge=1)):
    # 将数据的开仓时间设置为现在（未开仓的才能开仓）
    sql = 'UPDATE info_strategy SET open_time=%s WHERE id=%s AND creator=%s AND open_time is null;'
    db = FAConnection(conn_name='策略开仓')
    _, s = db.execute(sql, param=[datetime.datetime.now(), strategy_id, person['uid']], keep_conn=True)
    if s:
        # 查询策略，返回策略信息
        query_sql = 'SELECT a.id,a.create_time,a.type,a.variety_en,b.variety_name,a.description,a.open_time,' \
                    'a.close_time,a.close_way,a.profit,a.fund,a.strategy_price,a.target_price,a.cutloss_price,' \
                    'a.strategy ' \
                    'FROM info_strategy AS a INNER JOIN sys_variety AS b ON a.variety_en=b.variety_en ' \
                    'WHERE a.id=%s AND a.creator=%s;'
        s_item = db.query(query_sql, param=[strategy_id, person['uid']], fetchone=True)
        response_data = format_strategy_response(s_item)[0]
        return {'code': r_status.SUCCESS, 'message': '设置策略为开仓状态成功!', 'data': response_data}
    db.close()
    return {'code': r_status.UNKNOWN_ERROR, 'message': '设置失败, 原因未知!', 'data': {}}


class CloseBodyItem(BaseModel):
    close_flag: str
    profit: float


@strategy_api.put('/strategy/{strategy_id}/close/', summary='策略平仓')
async def open_strategy(person: dict = Depends(logged_require), strategy_id: int = Path(..., ge=1),
                        body_item: CloseBodyItem = Body(...)):
    if body_item.close_flag not in ['target', 'cutloss', 'forced']:
        return {'code': r_status.VALIDATE_ERROR, 'message': '参数错误!'}
    # 查询出数据
    # 查询策略，返回策略信息
    query_sql = 'SELECT a.id,a.create_time,a.type,a.variety_en,b.variety_name,a.description,a.open_time,' \
                'a.close_time,a.profit,a.fund,a.strategy_price,a.target_price,a.cutloss_price,a.strategy ' \
                'FROM info_strategy AS a INNER JOIN sys_variety AS b ON a.variety_en=b.variety_en ' \
                'WHERE a.id=%s AND a.creator=%s;'
    db = FAConnection(conn_name='平仓策略')
    s_item = db.query(query_sql, param=[strategy_id, person['uid']], fetchone=True, keep_conn=True)[0]
    if not s_item:

        return {'code': r_status.NOT_CONTENT, 'message': '没有这个策略记录!'}
    if s_item['type'] < 2:
        # 单边策略根据计算结果
        strategy = eval(s_item['strategy'])[0]
        if body_item.close_flag == 'target':  # 触发目标价  profit = |目标价 - 开仓价| * 手数 * 合约乘数
            profit = abs(round(float(s_item['target_price'] - s_item['strategy_price']), 4)) * strategy['hands'] * strategy['exchange_mul']
        elif body_item.close_flag == 'cutloss':  # 触发止损价
            profit = -abs(round(float(s_item['cutloss_price'] - s_item['strategy_price']), 4)) * strategy['hands'] * strategy['exchange_mul']
        else:  # 未建仓而平仓
            profit = 0
    else:  # 套利策略直接取用结果
        profit = body_item.profit
    # 更新收益结果和平仓时间
    update_sql = 'UPDATE info_strategy SET close_time=%s,profit=%s,close_way=%s WHERE id=%s;'
    _, s = db.execute(update_sql, param=[datetime.datetime.now(), profit, body_item.close_flag, strategy_id])
    if s:
        s_item['profit'] = profit
        s_item['close_time'] = datetime.datetime.now()
        s_item['close_way'] = body_item.close_flag
        response_data = format_strategy_response([s_item])[0]
        return {'code': r_status.SUCCESS, 'message': '策略结束成功!', 'data': response_data}
    db.close()
    return {'code': r_status.UNKNOWN_ERROR, 'message': '策略结束失败, 原因未知!', 'data': {}}
